﻿using System.Data;
using System;
using System.IO;
using System.Linq;

using System.Collections.Generic;
using Common.Utilities.Excel.Export.Merged;
using Common.Utilities.Excel.Import.EventArgument;
using Common.Utilities.Excel.Import;
using Common.Utilities.Excel.Export;
using Common.Utilities.Excel.Export.EventArgument;
using Common.Utilities.Excel.Util;
using System.Web;
using System.Text;
using Common.Utilities.Excel.Validator;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace Common.Utilities.Excel
{

    public partial class ExcelUtilities
    {
        #region 事件声明

        #region Import
        /// <summary>
        /// 开始导入Excel文件事件
        /// </summary>
        public event EventHandler<ExcelImportArgs> OnBeforeImportExcel;
        /// <summary>
        /// 在读取Worksheet前的事件
        /// </summary>
        public event EventHandler<ExcelImportWorksheetArgs> OnBeforeReadWorksheet;
        /// <summary>
        /// 在读取行之前的事件
        /// </summary>
        public event EventHandler<ExcelImportRowArgs> OnBeforeReadExcelRow;
        /// <summary>
        /// 在读取单元格之前的事件
        /// </summary>
        public event EventHandler<ExcelImportItemArgs> OnCellItemReading;
        /// <summary>
        /// 导入Excel文件后的事件
        /// </summary>
        public event EventHandler<ExcelImportArgs> OnAfterImportExcel;
        /// <summary>
        /// 读取Worksheet之后的事件
        /// </summary>
        public event EventHandler<ExcelImportWorksheetArgs> OnAfterWorksheetRead;
        /// <summary>
        /// 读取行之后的事件
        /// </summary>
        public event EventHandler<ExcelImportRowArgs> OnAfterExcelRowRead;

        #endregion

        #region Export
        /// <summary>
        /// 开始导出Excel文件事件
        /// </summary>
        public event EventHandler<ExcelExportArgs> OnBeforeExportExcel;
        /// <summary>
        /// 在写入Worksheet前的事件
        /// </summary>
        public event EventHandler<ExcelExportWorksheetArgs> OnBeforeWriteWorksheet;
        /// <summary>
        /// 在写入Area前的事件
        /// </summary>
        public event EventHandler<ExcelExportAreaArgs> OnBeforeWriteArea;
        /// <summary>
        /// 在写入行之前的事件
        /// </summary>
        public event EventHandler<ExcelExportRowArgs> OnBeforeWriteExcelRow;
        /// <summary>
        /// 在写入单元格之前的事件
        /// </summary>
        public event EventHandler<ExcelExportItemArgs> OnCellItemWriting;
        /// <summary>
        /// 写入Excel文件后的事件
        /// </summary>
        public event EventHandler<ExcelExportArgs> OnAfterExportExcel;
        /// <summary>
        /// 写入Worksheet之后的事件
        /// </summary>
        public event EventHandler<ExcelExportWorksheetArgs> OnAfterWorksheetWritten;
        /// <summary>
        /// 在写入Area前的事件
        /// </summary>
        public event EventHandler<ExcelExportAreaArgs> OnAfterAreaWritten;
        /// <summary>
        /// 写入行之后的事件
        /// </summary>
        public event EventHandler<ExcelExportRowArgs> OnAfterExcelRowWritten;
        #endregion

        #endregion

        #region 属性声明

        /// <summary>
        /// object 数组, 供外部使用, 可以在调用导入或导出
        /// 方法前, 把需要的数据存放到此属性中, 在事件中
        /// 通过sender.Parameters获取
        /// </summary>
        public List<object> Parameters
        {
            get;
            set;
        }

        #endregion

        #region ctor

        public ExcelUtilities()
        {
            this.Parameters = new List<object>();
        }

        #endregion

        #region Import
        /// <summary>
        /// 将指定的Excel文件数据导入到Excel中,注意(容器会被清空)
        /// </summary>
        /// <param name="xlsFileName">Excel源文件</param>
        /// <param name="typeName">导入的配置类型名</param>
        /// <param name="excel">数据源</param>
        /// <returns></returns>
        public virtual ExcelResult ImportExcel(String xlsFileName, string typeName, out Import.Excel excel)
        {
            return ImportExcel(xlsFileName, null, typeName, out excel);
        }

        /// <summary>
        /// 将指定的Excel文件数据导入到Excel中,注意(容器会被清空)
        /// </summary>
        /// <param name="xlsFileName">Excel源文件</param>
        /// <param name="errFileName">Excel错误提示文件</param>
        /// <param name="typeName">导入的配置类型名</param>
        /// <param name="excel">数据源</param>
        /// <returns></returns>
        public virtual ExcelResult ImportExcel(String xlsFileName, String errFileName, string typeName, out Import.Excel excel)
        {
            using (FileStream inputStream = new FileStream(xlsFileName, FileMode.Open))
            {
                Stream outputStream = null;
                if(!String.IsNullOrEmpty(errFileName))
                {
                    outputStream = new FileStream(errFileName, FileMode.Open);
                }
                return ImportExcel(inputStream, outputStream, typeName, out excel);
            }
        }

        /// <summary>
        /// 将指定的Excel文件数据导入到Excel中,注意(容器会被清空)
        /// </summary>
        /// <param name="inputStream">Excel数据流</param>
        /// <param name="response">当验证失败后，将标注了提示信息的Excel返回到客户端</param>
        /// <param name="fileName">文件名，不包含.xls</param>
        /// <param name="typeName">导入的配置类型名</param>
        /// <param name="excel">数据源</param>
        /// <returns></returns>
        public virtual ExcelResult ImportExcel(Stream inputStream, HttpResponse response, String fileName, string typeName, out Import.Excel excel)
        {
            MemoryStream memory = new MemoryStream();
            var result = ImportExcel(inputStream, memory, typeName, out excel);
            if (result.Result != ExcelResultType.Succeed)
            {
                memory.Position = 0;

                response.Clear();
                response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlPathEncode(fileName + ".xls"));
                response.AddHeader("Content-Type", "application/force-download");
                response.AddHeader("Content-Type", "application/octet-stream");
                response.AddHeader("Content-Type", "application/download");
                response.AddHeader("Content-Transfer-Encoding", "binary");
                response.ContentType = "application/ms-excel";
                response.BinaryWrite(memory.GetBuffer());
                response.End();
            }
            return result;
        }

        /// <summary>
        /// 将指定的Excel文件数据导入到Excel中,注意(容器会被清空)
        /// </summary>
        /// <param name="inputStream">Excel源文件输入流</param>
        /// <param name="outStream">Excel错误文件输出流</param>
        /// <param name="typeName">导入的配置类型名</param>
        /// <param name="excel">数据源</param>
        /// <returns></returns>
        public virtual ExcelResult ImportExcel(Stream inputStream, Stream outStream, string typeName, out Import.Excel excel)
        {
            excel = new Import.Excel()
            {
                Worksheets = new List<Import.Worksheet>()
            };

            // 保存处理结果
            ExcelResult result = new ExcelResult();

            // 读取导入Excel的配置信息
            ExcelImportProvider provider = ExcelImportConfiguration.Current.ExcelConfigs[typeName];

            // 验证配置节是否包含错误
            ConfigImportValidate(provider, typeName);

            #region OnBeforeImportExcel
            ///所有的业务验证通过后，开始执行导入,导入之前可提供外部开发人员相应导入前的准备事件工作
            ExcelImportArgs excelImportArgs = new ExcelImportArgs(excel, result.ExcelMessages);
            if (OnBeforeImportExcel != null)
            {
                OnBeforeImportExcel(this, excelImportArgs);
                ///如果外部程序此委托事件返回false,则取消导入
                if (excelImportArgs.Canceled)
                {
                    ExcelMessageFactory.PrepareEventMessage(result, "OnBeforeImportExcel", excelImportArgs.Canceled);
                    return result;
                }
            }
            #endregion

            // 根据文件路径获取Workbook对象
            IWorkbook workbook = WorkbookFactory.Create(inputStream);

            #region 循环读取配置文件中worksheet页
            foreach (Import.Worksheet item in provider.Worksheets)
            {
                #region Worksheet检查
                // 获取一个Worksheet对象的副本信息
                Import.Worksheet worksheet = item.GetCopyWorkSheet();

                // 根据名称获取Sheet对象
                ISheet sheet = GetSheet(workbook, worksheet);

                // 如果Excel中不存在对应的worksheet, 则记录异常
                if (sheet == null)
                {
                    result.ExcelMessages.Add(ExcelMessageFactory.GenerateWorksheet(
                        string.Format("Excel文件中无法找到名称为 {0} 的Worksheet", worksheet.Name), true));
                    result.Result = ExcelResultType.Canceled;

                    return result;
                }
                #endregion

                #region OnBeforeReadWorksheet
                ExcelImportWorksheetArgs sheetArgs = new ExcelImportWorksheetArgs(sheet, worksheet, result.ExcelMessages);
                if (OnBeforeReadWorksheet != null)
                {
                    OnBeforeReadWorksheet(this, sheetArgs);
                    string eventName = "OnBeforeReadWorksheet";
                    ExcelMessageFactory.PrepareSheetMessage(result, worksheet.Name, sheetArgs.WorksheetStatus, eventName);
                    if (sheetArgs.WorksheetStatus == WorksheetStatus.Skip)
                        continue;
                    else if (sheetArgs.WorksheetStatus == WorksheetStatus.Canceled)
                        return result;
                }
                #endregion

                int startColIdx = worksheet.Column; // 工作表开始列号
                int startRowIdx = worksheet.Row; // 工作表开始行号
                int lastRowIdx = sheet.LastRowNum; // 工作表最后一行号
                int iLoopRow = -1; // 当前读取Excel的行
                int jLoopCol = -1; // 当前读取Excel的列

                #region 循环读取Excel行信息
                for (iLoopRow = startRowIdx; iLoopRow < lastRowIdx + 1; iLoopRow++)
                {
                    IRow row = sheet.GetRow(iLoopRow); // Execl的行对象

                    Dictionary<string, object> dataRow = new Dictionary<string, object>();

                    bool isLegalRowResult = true; // 行数据是否合法

                    // 调用事件函数OnBeforeReadExcelRow
                    #region OnBeforeReadExcelRow
                    // 事件参数
                    ExcelImportRowArgs rowArgs = new ExcelImportRowArgs(dataRow, row, sheet, worksheet, result.ExcelMessages);
                    if (OnBeforeReadExcelRow != null)
                    {
                        string eventName = "OnBeforeReadExcelRow";
                        OnBeforeReadExcelRow(this, rowArgs);
                        ExcelMessageFactory.PrepareRowMessage(result, iLoopRow, rowArgs.RowStatus, eventName);
                        if (rowArgs.RowStatus == RowStatus.Canceled) return result;
                        else if (rowArgs.RowStatus == RowStatus.Finish) break;
                        else if (rowArgs.RowStatus == RowStatus.Skip) continue;
                    }

                    #endregion

                    #region 循环读取配置文件worksheet页中DataColumn列信息
                    foreach (Import.DataColumn dataColunm in worksheet.DataColumns)
                    {
                        // 重设默认值, 使其不再使用上一行所使用的值
                        dataColunm.SetDefaultNull();
                        // 根据列偏移获取列序号
                        jLoopCol = startColIdx + dataColunm.Offset;

                        ICell cell = row.GetCell(jLoopCol); // Execl的单元格对象
                        if (cell == null)
                        {
                            cell = row.CreateCell(jLoopCol);
                        }

                        object cellValue = GetCellValue(cell, dataColunm); // Execl的单元格的值

                        // 验证数据格式
                        #region 验证数据格式
                        ValidatorResult validatorResult = new ValidatorResult();
                        if (!string.IsNullOrEmpty(dataColunm.Validator))
                        {
                            ValidatorConfig config = new ValidatorConfig()
                            {
                                Value = cellValue.ToString(),
                                Label = dataColunm.Label,
                                Validator = dataColunm.Validator
                            };

                            validatorResult = ValidatorUtilites.Validator(config);
                        }
                        #endregion

                        

                        // 正确则加入数据容器中，否则在Excel设置错误备注
                        if (validatorResult.Result)
                        {
                            // 给数据容器赋值
                            dataRow[dataColunm.Field] = cellValue;
                        }
                        else
                        {
                            #region 给单元格加备注
                            //// 给单元格加备注
                            

                            foreach (var msg in validatorResult.MessageList)
                            {
                                result.ExcelMessages.Add(new ExcelMessage()
                                {
                                    LogTime = DateTime.Now,
                                    IsException = false,
                                    Message = msg,
                                    MessageType = MessageType.Cell
                                });
                            }

                            // 设置红色填充色
                            ICellStyle errorStyle = workbook.CreateCellStyle();
                            errorStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;
                            errorStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;

                            // 字体
                            IFont font = workbook.CreateFont();
                            font.IsItalic = true;
                            font.Color = NPOI.HSSF.Util.HSSFColor.RED.index;
                            errorStyle.SetFont(font);

                            cell.CellStyle = errorStyle;

                            dataRow[dataColunm.Field] = null;
                            cell.SetCellValue(cellValue + "\n" + validatorResult.MessageList[0]);

                            // 导入文件失败
                            isLegalRowResult = false;
                            result.Result = ExcelResultType.Failed;
                            #endregion
                        }

                        #region OnCellItemReading
                        ExcelImportItemArgs itemArgs = new ExcelImportItemArgs(iLoopRow, jLoopCol, cellValue, dataColunm, cell, validatorResult, dataRow);
                        // 调用外部事件
                        if (OnCellItemReading != null)
                        {
                            OnCellItemReading(this, itemArgs);
                            ExcelMessageFactory.PrepareCellMessage(result, iLoopRow, jLoopCol, itemArgs.CellStatus, "OnCellItemReading");
                            if (itemArgs.CellStatus == CellStatus.Canceled)
                                return result;
                            else if (itemArgs.CellStatus == CellStatus.IngoreRow)
                                break;
                        }
                        #endregion

                    }
                    #endregion

                    // 行数据合法才添加数据
                    if (isLegalRowResult) worksheet.DataList.Add(dataRow);

                    // 调用事件函数OnAfterExcelRowRead
                    #region OnAfterExcelRowRead
                    if (OnAfterExcelRowRead != null)
                    {
                        rowArgs.RowStatus = RowStatus.Continue;
                        string eventName = "OnAfterExcelRowRead";
                        OnAfterExcelRowRead(this, rowArgs);
                        ExcelMessageFactory.PrepareRowMessage(result, iLoopRow, rowArgs.RowStatus, eventName);
                        if (rowArgs.RowStatus == RowStatus.Canceled) return result;
                        else if (rowArgs.RowStatus == RowStatus.Finish) break;
                        else if (rowArgs.RowStatus == RowStatus.Skip) continue;
                    }
                    #endregion
                }
                #endregion


                // 调用OnAfterWorksheetRead
                #region OnAfterWorksheetRead
                if (OnAfterWorksheetRead != null)
                {
                    sheetArgs.WorksheetStatus = WorksheetStatus.Continue;
                    OnAfterWorksheetRead(this, sheetArgs);
                    string eventName = "OnAfterWorksheetRead";
                    ExcelMessageFactory.PrepareSheetMessage(result, worksheet.Name, sheetArgs.WorksheetStatus, eventName);
                    if (sheetArgs.WorksheetStatus == WorksheetStatus.Skip) continue;
                    else if (sheetArgs.WorksheetStatus == WorksheetStatus.Canceled) return result;
                }
                #endregion

                excel.Worksheets.Add(worksheet);
            }
            #endregion

            // 调用OnAfterImportExcel
            #region OnAfterImportExcel
            if (OnAfterImportExcel != null)
            {
                excelImportArgs.Canceled = false;
                OnAfterImportExcel(this, excelImportArgs);
                ///如果外部程序此委托事件返回false,则取消导入
                if (excelImportArgs.Canceled)
                {
                    result.ExcelMessages.Add(ExcelMessageFactory.GenerateEvent("在事件OnAfterImportExcel中被取消", true));
                    result.Result = ExcelResultType.Canceled;
                    return result;
                }
            }
            #endregion

            if (result.Result == ExcelResultType.Failed && outStream != null)
            {
                // save
                workbook.Write(outStream);
            }

            return result;
        }

        /// <summary>
        /// 获取Sheet对象
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="worksheet"></param>
        /// <returns>如果没有指定Worksheet的名称,则返回第一个Worksheet</returns>
        private static ISheet GetSheet(IWorkbook workbook, Import.Worksheet worksheet)
        {
            ISheet sheet;
            // 如果没有指定worksheet的名字, 则取第一个
            if (string.IsNullOrEmpty(worksheet.Name))
            {
                sheet = workbook.GetSheetAt(0);
            }
            else
            {
                sheet = workbook.GetSheet(worksheet.Name);
            }
            return sheet;
        }

        /// <summary>
        /// 根据文件路径获取Workbook对象
        /// </summary>
        /// <param name="excelFile"></param>
        /// <returns></returns>
        private static IWorkbook GetWorkbook(string excelFile)
        {
            using (FileStream file = new FileStream(excelFile, FileMode.Open, FileAccess.ReadWrite))
            {
                return WorkbookFactory.Create(file);
            }
        }

        /// <summary>
        /// 验证配置节是否包含错误
        /// </summary>
        /// <typeparam name="C"></typeparam>
        /// <param name="provider"></param>
        /// <param name="excelFile"></param>
        /// <param name="typeName"></param>
        /// <returns></returns>
        protected virtual bool ConfigImportValidate(ExcelImportProvider provider, string typeName)
        {
           
            ///验证导入配置中的配置节是否存在
            if (provider == null) throw new Exception(string.Format("配置中不存在类型为:{0}的导入配置项!", typeName));
            ///验证配置节中是否包含至少1个worksheet
            if (provider.Worksheets == null || provider.Worksheets.Count == 0) throw new Exception(string.Format("配置中类型为:{0}的配置节中不包含Worksheet的定义", typeName));

            //验证配置节中只能包含一个名字为空的worksheet
            int nullNameWorksheetCount = 0;
            if ((nullNameWorksheetCount = provider.Worksheets.Count(p => string.IsNullOrEmpty(p.Name))) > 1) throw new Exception(string.Format("配置中类型为:{0}的配置节中存在 {1} 项Worksheet名字为空的项", typeName, nullNameWorksheetCount));

            return true;
        }

        /// <summary>
        /// 获取列的值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="dataColumn"></param>
        /// <returns></returns>
        protected object GetCellValue(ICell cell, Import.DataColumn dataColumn)
        {
            // 不需要从Excel绑定数据的, 直接取默认值
            if (!dataColumn.NeedBind) return dataColumn.DefaultValue;

            ICell valueCell = cell;

            // 存在合并单元格情况
            if (dataColumn.HasMerged)
            {
                valueCell = GetRealValueCell(cell);
            }

            if (string.IsNullOrEmpty(dataColumn.Match))
            {
                switch (dataColumn.DataType)
                {
                    case Import.DataTypeEnums.String:
                        try
                        {
                            string value = valueCell.StringCellValue;
                            return value;
                        }
                        catch
                        {
                            try
                            {
                                return valueCell + "";
                            }
                            catch (Exception)
                            {
                                return null;
                            }
                        }
                    case Import.DataTypeEnums.Numeric:
                        try
                        {
                            if (valueCell + "" == "")
                                return null;
                            double value = valueCell.NumericCellValue;
                            return value;
                        }
                        catch
                        {
                            return null;
                        }
                    case Import.DataTypeEnums.DateTime:
                        try
                        {
                            string cellContent = valueCell + "";
                            if (string.IsNullOrEmpty(cellContent))
                            {
                                return null;
                            }
                            DateTime dateTime;
                            bool parseResult = DateTime.TryParse(cellContent, out dateTime);
                            if (parseResult)
                            {
                                return dateTime;
                            }
                            else
                            {
                                DateTime value = valueCell.DateCellValue;
                                return value;
                            }
                        }
                        catch
                        {
                            return null;
                        }
                    default:
                        return null;
                }
            }
            else
            {
                return dataColumn.GetMatchValue(valueCell);
            }
        }

        /// <summary>
        /// 如果在合并单元格区间内，则返回区间第一个单元格；否则返回自己
        /// </summary>
        /// <param name="cell"></param>
        /// <returns>返回一个单元格</returns>
        private ICell GetRealValueCell(ICell cell)
        {
            ISheet sheet = cell.Sheet;
            int rowIndex = cell.RowIndex;
            int colIndex = cell.ColumnIndex;//.CellNum;

            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                var region = sheet.GetMergedRegion(i);
                //应该修改为只是落入合并单元格的区域内则认为是合并单元格，返回第一个表格
                if (rowIndex >= region.FirstRow && rowIndex <= region.LastRow && colIndex >= region.FirstColumn && colIndex <= region.LastColumn)
                {
                    return sheet.GetRow(region.FirstRow).GetCell(region.FirstColumn);
                }
            }

            return cell;
        }

        #endregion

        #region Export
        /// <summary>
        /// 导出DataSet中的内容到Excel中
        /// </summary>
        /// <param name="container">保存数据的对象类型</param>
        /// <param name="excelFile">导出的目标文件</param>
        /// <param name="typeName">导出的类型</param>
        /// <param name="templateFile">导出的类型</param>
        /// <returns>导出成功返回True,否则false</returns>
        public virtual ExcelResult ExportExcel(DataSet container, string excelFile, string typeName)
        {
            return ExportExcel(container, excelFile, typeName, null);
        }
        /// <summary>
        /// 导出DataSet中的内容到Excel中
        /// </summary>
        /// <param name="container">保存数据的对象类型</param>
        /// <param name="excelFile">导出的目标文件</param>
        /// <param name="typeName">导出的类型</param>
        /// <param name="templateFile">导出的类型</param>
        /// <returns>导出成功返回True,否则false</returns>
        public virtual ExcelResult ExportExcel(DataSet container, string excelFile, string typeName, string templateFile)
        {
            ExcelExportProvider provider = ExcelExportConfiguration.Current.ExcelConfigs[typeName];
            
            // 导入配置文件验证
            ConfigExportValidate(provider, container, typeName);
           
            ExcelResult result = new ExcelResult();

            /// 1. 打开excel文件, 如果templateFile为空, 则取预配置的templateFile
            string fileName;
            if (!string.IsNullOrEmpty(templateFile))
                fileName = templateFile;
            else if (!string.IsNullOrEmpty(provider.TemplateFile))
            {
                HttpContext context = HttpContext.Current;
                if (context != null)
                {
                    fileName = context.Server.MapPath("~/" + provider.TemplateFile);
                }
                else
                {
                    fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, provider.TemplateFile);
                }
            }
            else
                throw new Exception("参数excelFile及预设的TemplateFile都为空，无法打开Excel文件");

            // 打开模板文件
            FileStream sourcefs = new FileStream(fileName, FileMode.Open, FileAccess.Read);

            IWorkbook workbook = WorkbookFactory.Create(sourcefs);

            provider.Workbook = workbook;


            #region OnBeforeExportExcel 事件

            ExcelExportArgs xlsEptArgs = new ExcelExportArgs(fileName, container, result.ExcelMessages,provider);
            if (OnBeforeExportExcel != null)
            {
                OnBeforeExportExcel(this, xlsEptArgs);

                if (xlsEptArgs.Canceled)
                {
                    ExcelMessageFactory.PrepareEventMessage(result, "OnBeforeExportExcel", xlsEptArgs.Canceled);
                    return result;
                }
            }
            #endregion

            /// 遍历每个Worksheet, 如果不存在对应名字的Worksheet, 则抛出异常
            foreach (Export.Worksheet worksheet in provider.Worksheets)
            {
                ISheet sheet = workbook.GetSheet(worksheet.Name);

                //如果不存在的情况下，则动态创建一个worksheet;
                if (sheet == null)
                {
                    sheet = workbook.CreateSheet(worksheet.Name);
                    ISheet oldSheet = workbook.GetSheet(worksheet.RefSheetName);
                    if (oldSheet==null)
                        throw new Exception(string.Format("不存在Sheet名称为{0}的工作表", worksheet.RefSheetName));

                    NpoiUtil.CopySheet(oldSheet, workbook, sheet);
                }
                worksheet.Sheet = sheet;

                #region OnBeforeWriteWorksheet 事件

                ExcelExportWorksheetArgs xlsEptSheetArgs = new ExcelExportWorksheetArgs(sheet, worksheet, result.ExcelMessages, container);
                if (OnBeforeWriteWorksheet != null)
                {
                    OnBeforeWriteWorksheet(this, xlsEptSheetArgs);
                    string eventName = "OnBeforeReadWorksheet";
                    ExcelMessageFactory.PrepareSheetMessage(result, worksheet.Name, xlsEptSheetArgs.WorksheetStatus, eventName);
                    if (xlsEptSheetArgs.WorksheetStatus == WorksheetStatus.Skip)
                        continue;
                    else if (xlsEptSheetArgs.WorksheetStatus == WorksheetStatus.Canceled)
                        return result;
                }

                #endregion

                /// 遍历每个Area, 把数据填入Area
                // 用于记录目前写入Excel的最大行数
                int totalRowSpan = worksheet.Row;
                foreach (Area area in worksheet.Areas)
                {
                    DataTable dataTable = container.Tables[area.DataTable];

                    #region OnBeforeWriteArea 事件

                    ExcelExportAreaArgs xlsAreaArgs = new ExcelExportAreaArgs(sheet, area, dataTable, result.ExcelMessages);
                    if (OnBeforeWriteArea != null)
                    {
                        OnBeforeWriteArea(this, xlsAreaArgs);
                        string eventName = "OnBeforeWriteArea";
                        ExcelMessageFactory.PrepareAreaMessage(result, worksheet.Name, xlsAreaArgs.AreaStatus, eventName);
                        if (xlsAreaArgs.AreaStatus == AreaStatus.Skip)
                            continue;
                        else if (xlsAreaArgs.AreaStatus == AreaStatus.Canceled)
                            return result;
                    }
                    #endregion

                    totalRowSpan += area.Row; // 最大行数更新至区域的行偏移量
                    int originRowSpan = totalRowSpan;

                    if (area.IsRepeat)
                    {
                        // 把此区域以下的行往下移动重复行的行数
                        if (sheet.GetRow(totalRowSpan) == null)
                            sheet.CreateRow(totalRowSpan);

                        if (!area.IsStaticArea&&dataTable.Rows.Count != 0)
                            sheet.ShiftRows(totalRowSpan, sheet.LastRowNum, dataTable.Rows.Count);

                        // 遍历数据行
                        RepeatArea repeatArea = area as RepeatArea;
                        foreach (DataRow dataRow in dataTable.Rows)
                        {
                            IRow row = GetRow(sheet, totalRowSpan);
                            bool breakFromIngoreRow = false;
                            #region OnBeforeWriteExcelRow 事件

                            ExcelExportRowArgs xlsRowArgs = new ExcelExportRowArgs(dataRow, row, sheet,workbook,
                                area, result.ExcelMessages);
                            if (OnBeforeWriteExcelRow != null)
                            {
                                OnBeforeWriteExcelRow(this, xlsRowArgs);
                                string eventName = "OnBeforeWriteExcelRow";
                                ExcelMessageFactory.PrepareRowMessage(result, row.RowNum, xlsRowArgs.RowStatus, eventName);
                                if (xlsRowArgs.RowStatus == RowStatus.Skip)
                                {
                                    SkipRow(sheet, totalRowSpan, row);
                                    continue;
                                }
                                else if (xlsRowArgs.RowStatus == RowStatus.Canceled)
                                    return result;
                            }

                            #endregion

                            // 遍历每个配置列, 读取数据, 往Excel插
                            foreach (Export.DataColumn dataColumn in (repeatArea).DataColumns)
                            {
                                ICell cell = GetCell(row, worksheet.Column + area.Column + dataColumn.Offset);

                                // 设置DataRow到此列
                                dataColumn.DataRow = dataRow;

                                object oldValue, newValue;
                                oldValue = newValue = dataColumn.GetCellPrepareValue(cell, dataRow);

                                #region OnCellItemWriting
                                ExcelExportItemArgs xlsItemArgs = new ExcelExportItemArgs(cell.RowIndex, cell.ColumnIndex,
                                    oldValue, newValue, dataColumn, cell, result.ExcelMessages);
                                if (OnCellItemWriting != null)
                                {
                                    OnCellItemWriting(this, xlsItemArgs);
                                    breakFromIngoreRow = ExcelMessageFactory.PrepareCellMessage(result, cell.RowIndex, cell.ColumnIndex, xlsItemArgs.CellStatus, "OnCellItemWriting");
                                    if (xlsItemArgs.CellStatus == CellStatus.IngoreRow)
                                    {
                                        SkipRow(sheet, totalRowSpan, row);
                                        break;
                                    }
                                    else if (xlsItemArgs.CellStatus == CellStatus.Canceled)
                                        return result;
                                }
                                #endregion

                                dataColumn.SetCellValue(cell, xlsItemArgs.NewValue, dataRow);
                            }

                            if (!breakFromIngoreRow)
                            {
                                #region OnAfterExcelRowWritten 事件

                                xlsRowArgs = new ExcelExportRowArgs(dataRow, row, sheet,workbook,
                                    area, result.ExcelMessages);
                                if (OnAfterExcelRowWritten != null)
                                {
                                    OnAfterExcelRowWritten(this, xlsRowArgs);
                                    string eventName = "OnAfterExcelRowWritten";
                                    ExcelMessageFactory.PrepareRowMessage(result, row.RowNum, xlsRowArgs.RowStatus, eventName);
                                    // 把已经添加的行移除
                                    if (xlsRowArgs.RowStatus == RowStatus.Skip)
                                    {
                                        SkipRow(sheet, totalRowSpan, row);
                                        continue;
                                    }
                                    else if (xlsRowArgs.RowStatus == RowStatus.Canceled)
                                        return result;
                                }
                                #endregion

                                // 行写入结束, 这时才可以进行列单元格合并

                                foreach (Export.DataColumn dataColumn in (repeatArea).DataColumns)
                                {
                                    // 列的MergeTop为True 或者 非区域第一行
                                    if ((repeatArea.MergeTop.ContainsKey(dataColumn.ID) && repeatArea.MergeTop[dataColumn.ID]) || totalRowSpan != originRowSpan)
                                    {
                                        if (dataColumn.IsMergeColumn)
                                        {
                                            int columnIndex = worksheet.Column + area.Column + dataColumn.Offset;
                                            ICell cell = GetCell(row, columnIndex);
                                            ICell cell_1 = GetCell(GetRow(sheet, row.RowNum - 1), columnIndex);
                                            if (cell + "" == cell_1 + "")
                                            {
                                                // 把当前单元格和上一行的单元格合并
                                                sheet.AddMergedRegion(new CellRangeAddress(row.RowNum - 1,
                                                                                     row.RowNum,
                                                                                     cell.ColumnIndex, 
                                                                                     cell.ColumnIndex));
                                            }
                                        }
                                    }
                                }

                                


                                totalRowSpan++; // 每写入一行， 行数便加1
                            }
                        }
                        area.RowSpan = totalRowSpan - originRowSpan;

                        List<MergedConfig> mergedconfigs = worksheet.MergedConfigs.Where(config => config.AreaId == area.ID).ToList();
                        if(mergedconfigs!=null&&mergedconfigs.Count>0)
                        {
                            // 开始行和结束行程序自动设置
                            Merged merged = new Merged();
                            merged.CurrentSheet = sheet;
                            merged.MergedConfigs = mergedconfigs;
                            merged.StartRow = originRowSpan;
                            merged.EndRow = totalRowSpan;
                            merged.DoMerged();
                        }
                    } 
                    else
                    {
                        // 非重复区间
                        if (dataTable.Rows.Count > 0)
                        {
                            DataRow dataRow = dataTable.Rows[0];
                            // 遍历每个配置单元格, 读取数据, 往Excel插
                            foreach (Export.DataCell dataCell in (area as StaticArea).DataCells)
                            {
                                ICell cell = GetCell(GetRow(sheet, totalRowSpan + dataCell.Row),
                                    worksheet.Column + area.Column + dataCell.Column);

                                // 设置DataRow到此单元格
                                dataCell.DataRow = dataRow;

                                #region OnCellItemWriting

                                object oldValue, newValue;
                                oldValue = newValue = dataCell.GetCellPrepareValue(cell, dataRow);
                                ExcelExportItemArgs xlsItemArgs = new ExcelExportItemArgs(cell.RowIndex, cell.ColumnIndex,
                                    oldValue, newValue, dataCell, cell, result.ExcelMessages);
                                if (OnCellItemWriting != null)
                                {
                                    OnCellItemWriting(this, xlsItemArgs);
                                    ExcelMessageFactory.PrepareCellMessage(result, cell.RowIndex, cell.ColumnIndex, xlsItemArgs.CellStatus, "OnCellItemWriting");
                                    if (xlsItemArgs.CellStatus == CellStatus.IngoreRow) // 这里不是跳过整行, 而只是跳过当前单元格
                                        continue;
                                    else if (xlsItemArgs.CellStatus == CellStatus.Canceled)
                                        return result;
                                }

                                #endregion

                                dataCell.SetCellValue(cell, xlsItemArgs.NewValue, dataRow);
                            }
                        }
                        totalRowSpan += (area as StaticArea).RowSpan; // 最大行数更新, 加上此区域的所跨越的行数
                    }
                    #region OnAfterAreaWritten 事件

                    xlsAreaArgs = new ExcelExportAreaArgs(sheet, area, dataTable, result.ExcelMessages);
                    if (OnAfterAreaWritten != null)
                    {
                        OnAfterAreaWritten(this, xlsAreaArgs);
                        string eventName = "OnAfterAreaWritten";
                        ExcelMessageFactory.PrepareAreaMessage(result, worksheet.Name, xlsAreaArgs.AreaStatus, eventName);
                        if (xlsAreaArgs.AreaStatus == AreaStatus.Skip)
                        {
                            continue;
                        }
                        else if (xlsAreaArgs.AreaStatus == AreaStatus.Canceled)
                            return result;
                    }

                    #endregion

                }

                #region OnAfterWorksheetWritten 事件

                xlsEptSheetArgs = new ExcelExportWorksheetArgs(sheet, worksheet, result.ExcelMessages, container);
                if (OnAfterWorksheetWritten != null)
                {
                    OnAfterWorksheetWritten(this, xlsEptSheetArgs);
                    string eventName = "OnAfterWorksheetWritten";
                    ExcelMessageFactory.PrepareSheetMessage(result, worksheet.Name, xlsEptSheetArgs.WorksheetStatus, eventName);
                    if (xlsEptSheetArgs.WorksheetStatus == WorksheetStatus.Skip)
                        continue;
                    else if (xlsEptSheetArgs.WorksheetStatus == WorksheetStatus.Canceled)
                        return result;
                }

                #endregion

                // 列宽自适应
                worksheet.AutoSizeColumn(sheet);
                // 强制公式计算
                sheet.ForceFormulaRecalculation = true;
            }

            #region OnAfterExportExcel

            xlsEptArgs = new ExcelExportArgs(fileName, container, result.ExcelMessages,provider);
            if (OnAfterExportExcel != null)
            {
                OnAfterExportExcel(this, xlsEptArgs);
                if (xlsEptArgs.Canceled)
                {
                    ExcelMessageFactory.PrepareEventMessage(result, "OnAfterExportExcel", xlsEptArgs.Canceled);
                    return result;
                }
            }
            #endregion

            // 创建保存文件路径
            string saveDirectory = excelFile.Substring(0, excelFile.LastIndexOf("\\"));
            if (!Directory.Exists(saveDirectory))
                Directory.CreateDirectory(saveDirectory);

            FileStream targetfs = new FileStream(excelFile, FileMode.Create, FileAccess.Write);
            workbook.Write(targetfs);

            // 赋结果值
            if (result.ExcelMessages.Count(p => p.IsException) == 0)
                result.Result = ExcelResultType.Succeed;
            else
                result.Result = ExcelResultType.CompletedWithException;

            sourcefs.Close();
            targetfs.Close();

            return result;
        }

        

        /// <summary>
        /// 浏览器导出装载着DataSet数据内容的Excel文件
        /// </summary>
        /// <param name="response">HttpResponse对象</param>
        /// <param name="saveAsFileName">导出的Excel文件名称</param>
        /// <param name="container">保存数据的容器</param>
        /// <param name="typeName">导出的类型</param>
        /// <param name="templateFile">导出的模板文件绝对地址</param>
        public virtual void ExportExcel(HttpResponse response, string saveAsFileName, DataSet container, string typeName)
        {
            ExportExcel(response, saveAsFileName, container, typeName, null);
        }

        /// <summary>
        /// 浏览器导出装载着DataSet数据内容的Excel文件
        /// </summary>
        /// <param name="response">HttpResponse对象</param>
        /// <param name="saveAsFileName">导出的Excel文件名称</param>
        /// <param name="container">保存数据的容器</param>
        /// <param name="typeName">导出的类型</param>
        /// <param name="templateFile">导出的模板文件绝对地址</param>
        public virtual void ExportExcel(HttpResponse response, string saveAsFileName, DataSet container, string typeName, string templateFile)
        {
            ExcelExportProvider provider = ExcelExportConfiguration.Current.ExcelConfigs[typeName];

            // 导入配置文件验证
            ConfigExportValidate(provider, container, typeName);

            ExcelResult result = new ExcelResult();

            /// 1. 打开excel文件, 如果templateFile为空, 则取预配置的templateFile
            string fileName;
            if (!string.IsNullOrEmpty(templateFile))
                fileName = templateFile;
            else if (!string.IsNullOrEmpty(provider.TemplateFile))
            { 
                HttpContext context = HttpContext.Current;
                if (context != null)
                {
                    fileName = context.Server.MapPath("~/" + provider.TemplateFile);
                }
                else
                {
                    fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, provider.TemplateFile);
                }
            }
            else
                throw new Exception("参数excelFile及预设的TemplateFile都为空，无法打开Excel文件");

            // 打开模板文件
            FileStream sourcefs = new FileStream(fileName, FileMode.Open, FileAccess.Read);


            IWorkbook workbook = WorkbookFactory.Create(sourcefs);

            provider.Workbook = workbook;


            #region OnBeforeExportExcel 事件

            ExcelExportArgs xlsEptArgs = new ExcelExportArgs(fileName, container, result.ExcelMessages, provider);
            if (OnBeforeExportExcel != null)
            {
                OnBeforeExportExcel(this, xlsEptArgs);

                if (xlsEptArgs.Canceled)
                {
                    ExcelMessageFactory.PrepareEventMessage(result, "OnBeforeExportExcel", xlsEptArgs.Canceled);
                    return ;
                }
            }
            #endregion

            /// 遍历每个Worksheet, 如果不存在对应名字的Worksheet, 则抛出异常
            foreach (Export.Worksheet worksheet in provider.Worksheets)
            {
                ISheet sheet = workbook.GetSheet(worksheet.Name);

                //如果不存在的情况下，则动态创建一个worksheet;
                if (sheet == null)
                {
                    sheet = workbook.CreateSheet(worksheet.Name);
                    ISheet oldSheet = workbook.GetSheet(worksheet.RefSheetName);
                    if (oldSheet == null)
                        throw new Exception(string.Format("不存在Sheet名称为{0}的工作表", worksheet.RefSheetName));

                    NpoiUtil.CopySheet(oldSheet, workbook, sheet);

                }
                worksheet.Sheet = sheet;

                #region OnBeforeWriteWorksheet 事件

                ExcelExportWorksheetArgs xlsEptSheetArgs = new ExcelExportWorksheetArgs(sheet, worksheet, result.ExcelMessages, container);
                if (OnBeforeWriteWorksheet != null)
                {
                    OnBeforeWriteWorksheet(this, xlsEptSheetArgs);
                    string eventName = "OnBeforeReadWorksheet";
                    ExcelMessageFactory.PrepareSheetMessage(result, worksheet.Name, xlsEptSheetArgs.WorksheetStatus, eventName);
                    if (xlsEptSheetArgs.WorksheetStatus == WorksheetStatus.Skip)
                        continue;
                    else if (xlsEptSheetArgs.WorksheetStatus == WorksheetStatus.Canceled)
                        return ;
                }

                #endregion

                /// 遍历每个Area, 把数据填入Area
                // 用于记录目前写入Excel的最大行数
                int totalRowSpan = worksheet.Row;
                foreach (Area area in worksheet.Areas)
                {
                    DataTable dataTable = container.Tables[area.DataTable];

                    #region OnBeforeWriteArea 事件

                    ExcelExportAreaArgs xlsAreaArgs = new ExcelExportAreaArgs(sheet, area, dataTable, result.ExcelMessages);
                    if (OnBeforeWriteArea != null)
                    {
                        OnBeforeWriteArea(this, xlsAreaArgs);
                        string eventName = "OnBeforeWriteArea";
                        ExcelMessageFactory.PrepareAreaMessage(result, worksheet.Name, xlsAreaArgs.AreaStatus, eventName);
                        if (xlsAreaArgs.AreaStatus == AreaStatus.Skip)
                            continue;
                        else if (xlsAreaArgs.AreaStatus == AreaStatus.Canceled)
                            return ;
                    }
                    #endregion

                    totalRowSpan += area.Row; // 最大行数更新至区域的行偏移量
                    int originRowSpan = totalRowSpan;

                    if (area.IsRepeat)
                    {
                        // 把此区域以下的行往下移动重复行的行数
                        if (sheet.GetRow(totalRowSpan) == null)
                            sheet.CreateRow(totalRowSpan);

                        if (!area.IsStaticArea && dataTable.Rows.Count != 0)
                            sheet.ShiftRows(totalRowSpan, sheet.LastRowNum, dataTable.Rows.Count);

                        // 遍历数据行
                        RepeatArea repeatArea = area as RepeatArea;
                        foreach (DataRow dataRow in dataTable.Rows)
                        {
                            IRow row = GetRow(sheet, totalRowSpan);
                            bool breakFromIngoreRow = false;
                            #region OnBeforeWriteExcelRow 事件

                            ExcelExportRowArgs xlsRowArgs = new ExcelExportRowArgs(dataRow, row, sheet, workbook,
                                area, result.ExcelMessages);
                            if (OnBeforeWriteExcelRow != null)
                            {
                                OnBeforeWriteExcelRow(this, xlsRowArgs);
                                string eventName = "OnBeforeWriteExcelRow";
                                ExcelMessageFactory.PrepareRowMessage(result, row.RowNum, xlsRowArgs.RowStatus, eventName);
                                if (xlsRowArgs.RowStatus == RowStatus.Skip)
                                {
                                    SkipRow(sheet, totalRowSpan, row);
                                    continue;
                                }
                                else if (xlsRowArgs.RowStatus == RowStatus.Canceled)
                                    return ;
                            }

                            #endregion

                            // 遍历每个配置列, 读取数据, 往Excel插
                            foreach (Export.DataColumn dataColumn in (repeatArea).DataColumns)
                            {
                                ICell cell = GetCell(row, worksheet.Column + area.Column + dataColumn.Offset);

                                // 设置DataRow到此列
                                dataColumn.DataRow = dataRow;

                                object oldValue, newValue;
                                oldValue = newValue = dataColumn.GetCellPrepareValue(cell, dataRow);

                                #region OnCellItemWriting
                                ExcelExportItemArgs xlsItemArgs = new ExcelExportItemArgs(cell.RowIndex, cell.ColumnIndex,
                                    oldValue, newValue, dataColumn, cell, result.ExcelMessages);
                                if (OnCellItemWriting != null)
                                {
                                    OnCellItemWriting(this, xlsItemArgs);
                                    breakFromIngoreRow = ExcelMessageFactory.PrepareCellMessage(result, cell.RowIndex, cell.ColumnIndex, xlsItemArgs.CellStatus, "OnCellItemWriting");
                                    if (xlsItemArgs.CellStatus == CellStatus.IngoreRow)
                                    {
                                        SkipRow(sheet, totalRowSpan, row);
                                        break;
                                    }
                                    else if (xlsItemArgs.CellStatus == CellStatus.Canceled)
                                        return ;
                                }
                                #endregion

                                dataColumn.SetCellValue(cell, xlsItemArgs.NewValue, dataRow);
                            }

                            if (!breakFromIngoreRow)
                            {
                                #region OnAfterExcelRowWritten 事件

                                xlsRowArgs = new ExcelExportRowArgs(dataRow, row, sheet, workbook,
                                    area, result.ExcelMessages);
                                if (OnAfterExcelRowWritten != null)
                                {
                                    OnAfterExcelRowWritten(this, xlsRowArgs);
                                    string eventName = "OnAfterExcelRowWritten";
                                    ExcelMessageFactory.PrepareRowMessage(result, row.RowNum, xlsRowArgs.RowStatus, eventName);
                                    // 把已经添加的行移除
                                    if (xlsRowArgs.RowStatus == RowStatus.Skip)
                                    {
                                        SkipRow(sheet, totalRowSpan, row);
                                        continue;
                                    }
                                    else if (xlsRowArgs.RowStatus == RowStatus.Canceled)
                                        return ;
                                }
                                #endregion

                                // 行写入结束, 这时才可以进行列单元格合并

                                foreach (Export.DataColumn dataColumn in (repeatArea).DataColumns)
                                {
                                    // 列的MergeTop为True 或者 非区域第一行
                                    if ((repeatArea.MergeTop.ContainsKey(dataColumn.ID) && repeatArea.MergeTop[dataColumn.ID]) || totalRowSpan != originRowSpan)
                                    {
                                        if (dataColumn.IsMergeColumn)
                                        {
                                            int columnIndex = worksheet.Column + area.Column + dataColumn.Offset;
                                            ICell cell = GetCell(row, columnIndex);
                                            ICell cell_1 = GetCell(GetRow(sheet, row.RowNum - 1), columnIndex);
                                            if (cell + "" == cell_1 + "")
                                            {
                                                // 把当前单元格和上一行的单元格合并
                                                sheet.AddMergedRegion(new CellRangeAddress(row.RowNum - 1,
                                                                                     row.RowNum,
                                                                                     cell.ColumnIndex, 
                                                                                     cell.ColumnIndex));
                                            }
                                        }
                                    }
                                }

                                totalRowSpan++; // 每写入一行， 行数便加1
                            }
                        }
                        area.RowSpan = totalRowSpan - originRowSpan;

                        List<MergedConfig> mergedconfigs = worksheet.MergedConfigs.Where(config => config.AreaId == area.ID).ToList();
                        if (mergedconfigs != null && mergedconfigs.Count > 0)
                        {
                            // 开始行和结束行程序自动设置
                            Merged merged = new Merged();
                            merged.CurrentSheet = sheet;
                            merged.MergedConfigs = mergedconfigs;
                            merged.StartRow = originRowSpan;
                            merged.EndRow = totalRowSpan;
                            merged.DoMerged();
                        }
                    }
                    else
                    {
                        // 非重复区间
                        if (dataTable.Rows.Count > 0)
                        {
                            DataRow dataRow = dataTable.Rows[0];
                            // 遍历每个配置单元格, 读取数据, 往Excel插
                            foreach (Export.DataCell dataCell in (area as StaticArea).DataCells)
                            {
                                ICell cell = GetCell(GetRow(sheet, totalRowSpan + dataCell.Row),
                                    worksheet.Column + area.Column + dataCell.Column);

                                // 设置DataRow到此单元格
                                dataCell.DataRow = dataRow;

                                #region OnCellItemWriting

                                object oldValue, newValue;
                                oldValue = newValue = dataCell.GetCellPrepareValue(cell, dataRow);
                                ExcelExportItemArgs xlsItemArgs = new ExcelExportItemArgs(cell.RowIndex, cell.ColumnIndex,
                                    oldValue, newValue, dataCell, cell, result.ExcelMessages);
                                if (OnCellItemWriting != null)
                                {
                                    OnCellItemWriting(this, xlsItemArgs);
                                    ExcelMessageFactory.PrepareCellMessage(result, cell.RowIndex, cell.ColumnIndex, xlsItemArgs.CellStatus, "OnCellItemWriting");
                                    if (xlsItemArgs.CellStatus == CellStatus.IngoreRow) // 这里不是跳过整行, 而只是跳过当前单元格
                                        continue;
                                    else if (xlsItemArgs.CellStatus == CellStatus.Canceled)
                                        return ;
                                }

                                #endregion

                                dataCell.SetCellValue(cell, xlsItemArgs.NewValue, dataRow);
                            }
                        }
                        totalRowSpan += (area as StaticArea).RowSpan; // 最大行数更新, 加上此区域的所跨越的行数
                    }
                    #region OnAfterAreaWritten 事件

                    xlsAreaArgs = new ExcelExportAreaArgs(sheet, area, dataTable, result.ExcelMessages);
                    if (OnAfterAreaWritten != null)
                    {
                        OnAfterAreaWritten(this, xlsAreaArgs);
                        string eventName = "OnAfterAreaWritten";
                        ExcelMessageFactory.PrepareAreaMessage(result, worksheet.Name, xlsAreaArgs.AreaStatus, eventName);
                        if (xlsAreaArgs.AreaStatus == AreaStatus.Skip)
                        {
                            continue;
                        }
                        else if (xlsAreaArgs.AreaStatus == AreaStatus.Canceled)
                            return ;
                    }

                    #endregion

                }

                #region OnAfterWorksheetWritten 事件

                xlsEptSheetArgs = new ExcelExportWorksheetArgs(sheet, worksheet, result.ExcelMessages, container);
                if (OnAfterWorksheetWritten != null)
                {
                    OnAfterWorksheetWritten(this, xlsEptSheetArgs);
                    string eventName = "OnAfterWorksheetWritten";
                    ExcelMessageFactory.PrepareSheetMessage(result, worksheet.Name, xlsEptSheetArgs.WorksheetStatus, eventName);
                    if (xlsEptSheetArgs.WorksheetStatus == WorksheetStatus.Skip)
                        continue;
                    else if (xlsEptSheetArgs.WorksheetStatus == WorksheetStatus.Canceled)
                        return ;
                }

                #endregion

                // 列宽自适应
                worksheet.AutoSizeColumn(sheet);
                // 强制公式计算
                sheet.ForceFormulaRecalculation = true;
            }

            #region OnAfterExportExcel

            xlsEptArgs = new ExcelExportArgs(fileName, container, result.ExcelMessages, provider);
            if (OnAfterExportExcel != null)
            {
                OnAfterExportExcel(this, xlsEptArgs);
                if (xlsEptArgs.Canceled)
                {
                    ExcelMessageFactory.PrepareEventMessage(result, "OnAfterExportExcel", xlsEptArgs.Canceled);
                    return ;
                }
            }
            #endregion

            // 写入内存
            MemoryStream file = new MemoryStream();
            workbook.Write(file);

            // 设置返回信息
            SetExcelResponse(response, file, saveAsFileName, null);
        }

        private void SetExcelResponse(HttpResponse response, MemoryStream file, string filename, Encoding encoding)
        {
            response.Clear();
            response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlPathEncode(filename));
            response.AddHeader("Content-Type", "application/force-download");
            response.AddHeader("Content-Type", "application/octet-stream");
            response.AddHeader("Content-Type", "application/download");
            response.AddHeader("Content-Transfer-Encoding", "binary");
            response.ContentType = "application/ms-excel";

            if (encoding != null)
            {
                response.ContentEncoding = encoding;
            }
            response.BinaryWrite(file.GetBuffer());
            response.End();
        }
        
        /// <summary>
        /// 跳过一行
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="totalRowSpan"></param>
        /// <param name="row"></param>
        private void SkipRow(ISheet sheet, int totalRowSpan, IRow row)
        {
            RemoveRow(sheet, row);
            if (sheet.GetRow(row.RowNum + 1) == null)
                sheet.CreateRow(row.RowNum + 1);
            sheet.ShiftRows(row.RowNum + 1, sheet.LastRowNum, -1);
        }

        /// <summary>
        /// 获取工作表的一行，如果不存在，则新建
        /// </summary>
        /// <param name="sheet">行所属的工作表</param>
        /// <param name="rowIndex">行索引</param>
        /// <returns>工作表的一行</returns>
        private IRow GetRow(ISheet sheet, int rowIndex)
        {
            IRow row = sheet.GetRow(rowIndex);
            if (row == null)
                row = sheet.CreateRow(rowIndex);
            return row;
        }

        private void RemoveRow(ISheet sheet, IRow row)
        {
            while (row.Cells.Count > 0)
            {
                row.RemoveCell(row.Cells[0]);
            }
            sheet.RemoveRow(row);
        }

        /// <summary>
        /// 获取行中的一单元格，如果不存在，则新建
        /// </summary>
        /// <param name="row">单元格所属的行</param>
        /// <param name="cellIndex">单元格索引</param>
        /// <returns>行中的一单元格</returns>
        private ICell GetCell(IRow row, int cellIndex)
        {
            ICell cell = row.GetCell(cellIndex);
            if (cell == null)
                cell = row.CreateCell(cellIndex);
            return cell;
        }

        protected void ConfigExportValidate(ExcelExportProvider provider, DataSet container, string typeName)
        {
            ///验证数据容器中是否存在DataTable
            if (container.Tables.Count == 0) throw new Exception("DataSet对象容器中没有任何DataTable对象!");
            ///验证导入配置中的配置节是否存在
            if (provider == null) throw new Exception(string.Format("配置中不存在类型为:{0}的导入配置项!", typeName));
            ///验证配置节中是否包含至少1个worksheet
            if (provider.Worksheets == null || provider.Worksheets.Count == 0) throw new Exception(string.Format("配置中类型为:{0}的配置节中不包含Worksheet的定义", typeName));
            // 验证数据容器中存在配置节中配置的datatable
            foreach (var worksheet in provider.Worksheets)
            {
                foreach (Area area in worksheet.Areas)
                    if (!container.Tables.Contains(area.DataTable))
                        throw new Exception(string.Format("DataTable中不包含表 {0}", area.DataTable));
            }
        }

        #endregion
    }
}
